##########################################################################################################################################
###### This is the file loads the merger database and prepares it for the later analysis
###############################

# set to your working directory:
setwd("C:\\Users\\Admin\\OneDrive\\Desktop\\Market Power and Systematic Risk - Data and Code")
getwd()

library(readxl)
##########
# To run this code, first download the merger database from Thomson Reuters / Refinitiv EIKON
# We have downloaded the data in pieces as it would otherwise be too large.
# The datafiles we have downloaded contain the following elements:
# Deal PermID,	SDC Deal No,	Date Announced,	Rank Date,	Rank Value inc. Net Debt of Target (USD, Millions)
# Target Full Name,	Target Nation,	Acquiror Full Name,	Acquiror Nation,	M&A Type,	Acquiror 6-digit CUSIP,
# Target 6-digit CUSIP,	Date Effective or Unconditional,	Common Shares Bought by Acquiror within 6 Months Prior to Announcement (Millions),
# Shares Tendered (Millions),	Tender Offer Flag,	Joint Venture Flag,	Merger of Equals Flag,	Purpose,
# Percentage of Shares Held at Announcement,	Percentage of Shares Acquired in Transaction,
# Regulatory Agencies Required to Approve Deal,
# Purpose Text,	Related MnA Deal Reason,	Rumored Deal Flag,	Date Acquisition of a Majority Interest is Effective,
# Strategic Purpose,	Sources of Funds,	Financing via Common Stock Offering Flag,
# Financing via Preferred Stock Offering Flag, Financing via Borrowings Flag, Deal Status
####

# this assumes that the merger files are in the directory D:\MandA\. Change if you use
# a different directory.
Merger_List = dir("D:\\MandA\\",pattern=".xlsx")

for(i in 1:length(Merger_List)){
  if(i == 1){
    Mergers <- data.frame(read_excel(paste0("D:\\MandA\\",Merger_List[i])))
  }else{
    Temp = data.frame(read_excel(paste0("D:\\MandA\\",Merger_List[i])))
    Mergers = rbind(Mergers,Temp)
  }
}

Mergers$Date.Announced = as.Date(Mergers$Date.Announced,format="%Y-%m-%d")
Mergers$Date.Effective.or.Unconditional = as.Date(Mergers$Date.Effective.or.Unconditional,format="%Y-%m-%d")

Duplicated_IDs = Mergers[duplicated(Mergers$Deal.PermID),]$Deal.PermID

Mergers_New = Mergers[!duplicated(Mergers$Deal.PermID),]

for(i in 1:length(Duplicated_IDs)){
  
  if(!all(is.na(Mergers[Mergers$Deal.PermID == Duplicated_IDs[i],]$Sources.of.Funds))){
    Mergers_New[Mergers_New$Deal.PermID == Duplicated_IDs[i],]$Sources.of.Funds = paste(Mergers[Mergers$Deal.PermID == Duplicated_IDs[i],]$Sources.of.Funds[!is.na(Mergers[Mergers$Deal.PermID == Duplicated_IDs[i],]$Sources.of.Funds)],sep=" ",collapse = ", ")
  }
  
  if(!all(is.na(Mergers[Mergers$Deal.PermID == Duplicated_IDs[i],]$Regulatory.Agencies.Required.to.Approve.Deal))){
    Mergers_New[Mergers_New$Deal.PermID == Duplicated_IDs[i],]$Regulatory.Agencies.Required.to.Approve.Deal = paste(Mergers[Mergers$Deal.PermID == Duplicated_IDs[i],]$Regulatory.Agencies.Required.to.Approve.Deal[!is.na(Mergers[Mergers$Deal.PermID == Duplicated_IDs[i],]$Regulatory.Agencies.Required.to.Approve.Deal)],sep=" ",collapse = ", ")
  }
  
  if(!all(is.na(Mergers[Mergers$Deal.PermID == Duplicated_IDs[i],]$Purpose))){
    Mergers_New[Mergers_New$Deal.PermID == Duplicated_IDs[i],]$Purpose = paste(Mergers[Mergers$Deal.PermID == Duplicated_IDs[i],]$Purpose[!is.na(Mergers[Mergers$Deal.PermID == Duplicated_IDs[i],]$Purpose)],sep=" ",collapse = ", ")
  }
  if((i%%floor(length(Duplicated_IDs)/10))==0){
    print(paste(i,"of",length(Duplicated_IDs),"this","is",i/length(Duplicated_IDs)*100,"Percent"))
  }
}
save(Mergers_New, file="Merger_Interm_save.RData")
